package org.wisdom.s3.job.service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Locale;

import javax.persistence.EntityManager;

import org.h2.util.StringUtils;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.wisdom.s3.job.domain.entity.SysConfig;
import org.wisdom.s3.job.domain.entity.SysUser;
import org.wisdom.s3.job.domain.repository.SysConfigRepository;
import org.wisdom.s3.job.domain.repository.SysUserRepository;
import org.wisdom.s3.job.feign.PeopleCenterFeign;

import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONObject;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;

/**
 * 同步数据
 *
 * @author 张智研
 * @date 2023/11/27 15:44
 **/

@Slf4j
@Service
@RequiredArgsConstructor
public class SyncDataService {

    private final PeopleCenterFeign peopleCenterFeign;

    private final SysConfigRepository sysConfigRepository;

    private final SysUserRepository sysUserRepository;

    private final EntityManager entityManager;

    private final JdbcTemplate jdbcTemplate;

    @Value("${spring.datasource.url}")
    private String url;
    @Value("${spring.datasource.driver-class-name}")
    private String driverClassName;
    @Value("${spring.datasource.username}")
    private String username;
    @Value("${spring.datasource.password}")
    private String password;

    /**
     * 用户信息同步
     * @author 张智研
     * @date 2024/02/29 14:13
     */
    @Scheduled(cron="0 15 23 ? * *")
    public void run() {
        // 查询用户最后更新时间
        SysConfig sysConfig = this.sysConfigRepository.queryByParmNameEn("PC_ASYNC_LASTDATE_USER");
        String lastUpdateTime = sysConfig.getParmValue();
        JSONObject param = new JSONObject();
        SimpleDateFormat dateformat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        if (!"-1".equals(lastUpdateTime) && !StringUtils.isNullOrEmpty(lastUpdateTime)) {
            try {
                param.set("updatef", sysConfig.getParmValue());

                long dateTime = System.currentTimeMillis();
                param.set("updatet", dateformat.format(dateTime));
                sysConfig.setParmValue(dateformat.format(dateTime));
            } catch (Exception e) {
                log.error(e.getMessage());
            }
        } else {
            param.set("updatef", "2012-12-30 00:00:00");
            param.set("updatet", "2012-12-30 12:00:00");
            sysConfig.setParmValue("2012-12-30 12:00:00");
        }

        int page = 1;
        String[] searchAfters = new String[1];
        searchAfters[0] = "";
        boolean lastPage = false;
        SysUser user;

        while (!lastPage) {
            param.set("page", page);
            param.set("searchAfters", searchAfters);
            log.info("发起{}接口请求，查询页码-请求参数：{}-{}", "查询员工", page, param);
            JSONObject result = peopleCenterFeign.employeeList(param);
            log.info("发起{}接口请求，查询页码-返回结果：{}-{}", "查询员工", page, result);
            if ("success".equalsIgnoreCase(result.getStr("code"))) {
                JSONObject data = result.getJSONObject("data");
                JSONArray empList = data.getJSONArray("list");
                if (empList == null || empList.size() <= 0) {
                    lastPage = true;
                    this.sysConfigRepository.save(sysConfig);
                    continue;
                }
                lastPage = data.getBool("lastPage");
                searchAfters[0] = data.getJSONArray("searchAfters").getStr(0);
                JSONObject tmpJsonObject;
                String eidCode;
                String employeeNumber;
                for (int i = 0; i < empList.size(); i++) {
                    tmpJsonObject = empList.getJSONObject(i);
                    eidCode = tmpJsonObject.getStr("username","");
                    employeeNumber = tmpJsonObject.getStr("employeenumber","空");

                    if (StringUtils.isNullOrEmpty(eidCode)) {
                        // 如果eid为空代表用户已离职，根据员工编码查询，修改删除标识和可用标识
                        List<SysUser> sysUserList = this.sysUserRepository.queryByEmployeeNumber(employeeNumber);
                        for (SysUser sysUser : sysUserList) {
                            sysUser.setDeletedFlag(1L);
                            sysUser.setDisabledFlag(false);
                            this.sysUserRepository.save(sysUser);
                        }
                        continue;
                    }
                    // 如果查询结果为已离职，则进行离职处理
                    if (!"Y".equals(tmpJsonObject.getStr("isactive").toUpperCase(Locale.ROOT))) {
                        List<SysUser> sysUserList = this.sysUserRepository.queryByEidCode(eidCode);
                        for (SysUser sysUser : sysUserList) {
                            sysUser.setDeletedFlag(1L);
                            sysUser.setDisabledFlag(false);
                            sysUser.setLastUpdateDate(new Date());
                            sysUser.setLastUpdatedBy("system");
                            this.sysUserRepository.save(sysUser);
                        }
                        continue;
                    }
                    // 查询用户
                    List<SysUser> sysUserList = this.sysUserRepository.queryByEidCode(eidCode);
                    if (!sysUserList.isEmpty()) {
                        user = sysUserList.get(0);
                        for (int j = 1; j < sysUserList.size(); j++) {
                            this.sysUserRepository.deleteById(sysUserList.get(j).getId());
                        }
                        user.setLastUpdateDate(new Date());
                        user.setLastUpdatedBy("system");
                    } else {
                        user = new SysUser();
                        user.setCreatedDate(new Date());
                        user.setCreatedBy("system");
                        user.setLastUpdateDate(new Date());
                        user.setLastUpdatedBy("system");
                        user.setDeletedFlag(0L);
                    }
                    user.setEidCode(eidCode);
                    user.setEmail(tmpJsonObject.getStr("email","空").replace("'", "''"));
                    user.setAccountId(tmpJsonObject.getStr("adid","空"));
                    user.setCompany(tmpJsonObject.getStr("companycode","空"));
                    user.setChineseName(tmpJsonObject.getStr("chinesename","空"));
                    user.setJobFamily(tmpJsonObject.getStr("jobfamily","空"));
                    user.setPositionName(tmpJsonObject.getStr("jobname","空"));
                    user.setEmployeeNumber(employeeNumber);
                    user.setSupervisorEmployeeNumber(tmpJsonObject.getStr("supervisoremployeenumber","空"));
                    user.setOrganizationName(tmpJsonObject.getStr("organizationname","空"));
                    user.setMobilePhone(tmpJsonObject.getStr("mobilephone","空"));

                    user.setTerminationDate(tmpJsonObject.getDate("terminationdate"));

                    user.setCostCenter(tmpJsonObject.getStr("costcenter","空"));
                    user.setGrade(tmpJsonObject.getStr("grade","空"));
                    user.setNationality(tmpJsonObject.getStr("nationality","空"));
                    user.setSupervisorChineseName(tmpJsonObject.getStr("supervisorchinesename","空"));
                    user.setSupervisorFirstName(tmpJsonObject.getStr("supervisorfirstname","空"));
                    user.setSupervisorLastName(tmpJsonObject.getStr("supervisorlastname","空"));
                    user.setEmployer(tmpJsonObject.getStr("employer","空"));
                    user.setAssignmentCategory(tmpJsonObject.getStr("assignmentcategory","空"));
                    user.setPersonType(tmpJsonObject.getStr("persontype","空"));
                    user.setOfficeAddress(tmpJsonObject.getStr("officeaddress","空"));
                    user.setFirstName(tmpJsonObject.getStr("firstname","空"));
                    user.setLastName(tmpJsonObject.getStr("lastname","空"));
                    user.setMeUpdateTime(new Date());
                    user.setRecordVersion(user.getRecordVersion() == null ? 0L : user.getRecordVersion() + 1);
                    user.setDisabledFlag(false);
                    user.setDeletedFlag(0L);
                    this.sysUserRepository.save(user);
                }
                this.sysConfigRepository.save(sysConfig);
            }
            page++;
        }
        //更新删除HK用户数据
        try { 
            SysConfig upSysConfig = this.sysConfigRepository.queryByParmNameEn("upUserHKSQL");
            String upSql = upSysConfig.getParmValue();
            this.jdbcTemplate.execute(upSql);
            SysConfig insertSysConfig = this.sysConfigRepository.queryByParmNameEn("asyncUserHKSQL");
            String insertSql = insertSysConfig.getParmValue();
            this.jdbcTemplate.execute(insertSql);
        } catch (Exception e) {
            log.error("HK同步用户数据异常", e);
        }
    }

    /**
     * 同步追踪任务信息
     * @author 张智研
     * @date 2024/02/29 14:13
     */
//    @Scheduled(cron="0 0 1 ? * *")
    @Transactional
    public void run1() {
        SysConfig sysConfig = this.sysConfigRepository.queryByParmNameEn("FAPIAO_TRACING_LASTDATE");
        // 最后更新时间
        String lastUpdateTime = sysConfig.getParmValue();
        SimpleDateFormat dateformat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        // 当前时间
        String now = dateformat.format(new Date());
        sysConfig.setParmValue(now);
        this.sysConfigRepository.save(sysConfig);
        String sql = this.getSql1();
        sql = sql.replace("?1",lastUpdateTime).replace("?2",now);

        Connection con = null;
        Statement stmt = null;
        try {
            con = DriverManager.getConnection(url, username, password);
            stmt = con.createStatement();
            stmt.execute(sql);
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage());
        } finally {
            if(stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 同步追踪任务发票信息
     * @author 张智研
     * @date 2024/02/29 14:13
     */
//    @Scheduled(cron="0 0 2 ? * *")
    @Transactional
    public void run2() {
        SysConfig sysConfig = this.sysConfigRepository.queryByParmNameEn("FAPIAO_TRACING_FP_LASTDATE");
        // 最后更新时间
        String lastUpdateTime = sysConfig.getParmValue();
        SimpleDateFormat dateformat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        // 当前时间
        String now = dateformat.format(new Date());
        sysConfig.setParmValue(now);
        this.sysConfigRepository.save(sysConfig);
        String sql = this.getSql2();
        sql = sql.replace("?1",lastUpdateTime).replace("?2",now);
        Connection con = null;
        Statement stmt = null;
        try {
            con = DriverManager.getConnection(url, username, password);
            stmt = con.createStatement();
            stmt.execute(sql);
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage());
        } finally {
            if(stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    private String getSql1() {
        return "MERGE T_FCT_FAPIAO_TRACING AS T USING (SELECT\n" +
                " (case when m1.CREATE_MODE = '500730016' then 'ecoin'\n" +
                " when m1.CREATE_MODE is not null then '其他'\n" +
                " when m1.CREATE_MODE is null and b.CREATED_DATE > '20190101 00:00:00' then 'sap re'\n" +
                " else '其他（历史）'\n" +
                " end)'SOURCING',\n" +
                " null 'type',\n" +
                " \n" +
                "( CASE WHEN ISNUMERIC(t.VOUCHER_AMOUNT)= 1  and PATINDEX('%[0-9]E%', t.VOUCHER_AMOUNT) > 0 THEN \n" +
                "  0\n" +
                "   WHEN ISNUMERIC(t.VOUCHER_AMOUNT)= 1 THEN \n" +
                "     CAST(replace(t.VOUCHER_AMOUNT,',','') AS Float ) \n" +
                "  ELSE 0 END )  'AMOUNT',\n" +
                "(case when PATINDEX('%[0-8]%', t.APPLICANT_DEPT) = 1 and ISNUMERIC(LEFT(t.APPLICANT_DEPT,7))= 1  \n" +
                "  then LEFT(t.APPLICANT_DEPT,7) else '' END)  'AMOUNT_CHARGE_OF_STORE',\n" +
                "(case when PATINDEX('%[0-8]%', t.APPLICANT_DEPT) = 1 \n" +
                "  and ISNUMERIC(LEFT(t.APPLICANT_DEPT,7))= 1 then null\n" +
                "  when PATINDEX('%[9]%', t.APPLICANT_DEPT) = 1 then LEFT(t.APPLICANT_DEPT,8) \n" +
                "  when s4.dept_name is not null then s4.DEPT_NUMBER else t.APPLICANT_DEPT\n" +
                "  end)\n" +
                "  'AMOUNT_CHARGE_OF_COMPANY',\n" +
                "  e.ENUM_CN_VALUE 'AMOUNT_TYPE',\n" +
                "(case when t.VENDOR_NO != '100' then 'CN'+t.VENDOR_NO else t.VENDOR_NO end) 'SUPPLIER_NO',-- 供应商编号\n" +
                "  t.VENDOR_NAME 'SUPPLIER_NAME',-- 供应商名称\n" +
                "  1 'TRACER_TYPE',-- 1：内部雇员 2：供应商\n" +
                "  null 'PAY_INFO',-- 对公或对私 内容：0 对公，1 对私\n" +
                "  r.FAPIAO_TRACKER 'TRACER_ID',-- 为用户EID 或 Vendor No.即被追踪的任务收接人ID\n" +
                "   (case when m1.CREATE_MODE = '500730016' then m1.task_no\n" +
                " else m.task_no\n" +
                " end) 'ORDER_ID',-- 该追踪任务对应前端订单编号，建立可追溯。如果是手工发起的编号时，用户手工输入对应的业务编号\n" +
                "  null 'trace_description', -- 取消说明，状态为取消必填\n" +
                "  null 'REMARK',-- 备注\n" +
                "(case when ISNUMERIC(b.attribute2) = 1 and LEN(b.attribute2) = 8 and b.attribute2 != '00000000' then b.attribute2 else null end)  'PAYMENT_TIME',-- 已付款时间\n" +
                "  ( CASE WHEN t.INVOICE_DESC IS NOT NULL THEN t.INVOICE_DESC ELSE b.INVOICE_DESC END ) 'PAYMENT_ABSTRACTS',-- 付款摘要\n" +
                "--  (case when m1.CREATE_MODE is null and b.CREATED_DATE > '20190101 00:00:00' then null\n" +
                "--  else t.VOUCHER_NO\n" +
                "--  end) 'VOUCHER_NO',-- 凭证号码\n" +
                " null 'VOUCHER_NO',-- 凭证号码\n" +
                "  b.FISCAL_PERIOD 'ACCTG_PERIOD',-- 会计期间\n" +
                "  t.COMPANY_NO 'COMPANY_CODE',-- 公司号\n" +
                "  m1.TASK_NO 'TASK_NO',-- 凭证任务号\n" +
                "  null 'COST_START_TIME',-- 费用期间起\n" +
                "  null 'COST_END_TIME',-- 费用期间止\n" +
                "(case when m.TASK_STATUS = 2 then 4 else 36 end)  'STATUS',-- 任务状态 \n" +
                "  t.VOUCHER_NO 'ORIGINAL_VOUCHER_NO',-- 原凭证编号\n" +
                "    (case when PATINDEX('%[0-8]%', t.APPLICANT_DEPT) = 1 and ISNUMERIC(LEFT(t.APPLICANT_DEPT,7))= 1 then s2.site_name\n" +
                "  when PATINDEX('%[9]%', t.APPLICANT_DEPT) = 1 then null\n" +
                "  else null END)  'STORE_NAME',-- 餐厅名称\n" +
                "  null 'PAYMENT_BLOCK',-- 冻结付款,H\n" +
                "  (case when LEN(t.VOUCHER_NO) >= 17 then SUBSTRING(t.VOUCHER_NO, 4, 4) else '' end)  'FISCAL_YEAR',-- 财年\n" +
                "    m.TASK_NO 'ORIGINAL_TASK_NUMBER',-- 原任务编号： s3任务编号\n" +
                "--    u1.bill_number 'BILL_NUMBER',-- 补票张数\n" +
                "null 'BILL_NUMBER',-- 补票张数\n" +
                "( CASE WHEN ISNUMERIC(t.VOUCHER_AMOUNT)= 1  and PATINDEX('%[0-9]E%', t.VOUCHER_AMOUNT) > 0 THEN \n" +
                "  0\n" +
                "   WHEN ISNUMERIC(t.VOUCHER_AMOUNT)= 1 THEN \n" +
                "     CAST(replace(t.VOUCHER_AMOUNT,',','') AS Float ) \n" +
                "  ELSE 0 END ) - ( CASE WHEN ISNUMERIC(t.DUE_AMOUNT)= 1  and PATINDEX('%[0-9]E%', t.DUE_AMOUNT) > 0 THEN \n" +
                "  0\n" +
                "   WHEN ISNUMERIC(t.DUE_AMOUNT)= 1 THEN \n" +
                "     CAST(replace(t.DUE_AMOUNT,',','') AS float ) \n" +
                "  ELSE 0 END ) 'PAID_AMOUNT',-- 已补发票金额\n" +
                "( CASE WHEN ISNUMERIC(t.DUE_AMOUNT)= 1  and PATINDEX('%[0-9]E%', t.DUE_AMOUNT) > 0 THEN \n" +
                "  0\n" +
                "   WHEN ISNUMERIC(t.DUE_AMOUNT)= 1 THEN \n" +
                "     CAST(replace(t.DUE_AMOUNT,',','') AS float ) \n" +
                "  ELSE 0 END )  'UNDERINVOICING_AMOUNT',-- 欠票金额\n" +
                "    m.CREATED_DATE 'creation_date', -- 任务创建时间\n" +
                "   ( CASE WHEN m.TASK_STATUS = '3' THEN m.LAST_UPDATE_DATE ELSE NULL END ) 'closed_date', -- 任务结束时间\n" +
                "    GETDATE() 'CREATED_DATE',\n" +
                "    GETDATE() 'LAST_UPDATE_DATE',\n" +
                "    (case when us.TERMINATION_DATE is not null then '已离职' else '在职' end) 'is_dimission',\n" +
                "  (case when LEN(t.VOUCHER_NO) >= 17 then SUBSTRING(t.VOUCHER_NO, 8, 20) else null end) 'short_voucher_no'\n" +
                "  from T_FORM_MAIN m WITH ( nolock )\n" +
                "LEFT JOIN T_FORM_FAPIAO_TRACING t WITH ( nolock ) ON m.ID = t.PARENT_FORM_ID\n" +
                "LEFT JOIN T_FORM_FAPIAO_TRA_RESP r WITH ( nolock ) ON r.FAPIAO_TRACING_ID = t.ID\n" +
                "LEFT JOIN T_SYS_ENUM_VALUE e WITH ( nolock ) ON e.ENUM_ITEM_ID = '30023' and e.ENUM_VALUE_NUM = t.FEE_TYPE\n" +
                "LEFT JOIN T_BAS_VOUCHER b WITH ( nolock ) ON t.VOUCHER_NO = b.VOUCHER_NO\n" +
                "LEFT JOIN (\n" +
                "  select task.VOUCHER_NO, max(task.TASK_NO) 'TASK_NO' from T_BAS_VOUCHER_TASK task WITH ( nolock ) \n" +
                "  where task.OFFSET_AMOUNT is null GROUP BY task.VOUCHER_NO\n" +
                ") vt ON vt.VOUCHER_NO = b.VOUCHER_NO\n" +
                "LEFT JOIN T_FORM_MAIN m1 WITH ( nolock ) on m1.TASK_NO = vt.TASK_NO\n" +
                "LEFT JOIN T_SYS_SITE s2 with(nolock) on s2.SITE_NUMBER=LEFT(t.APPLICANT_DEPT,7)\n" +
                "LEFT JOIN T_SYS_COMPANY_DEPT s3 with(nolock) on s3.DEPT_NUMBER=LEFT(t.APPLICANT_DEPT,8)\n" +
                "LEFT JOIN (SELECT \n" +
                "    task1.VOUCHER_NO, count(1) 'bill_number'\n" +
                "    FROM T_BAS_VOUCHER_TASK task1 WITH ( nolock ) \n" +
                "    GROUP BY task1.VOUCHER_NO\n" +
                "  ) u1 ON u1.VOUCHER_NO = t.VOUCHER_NO\n" +
                "LEFT JOIN T_SYS_USER us with(nolock) on us.EID_CODE = r.FAPIAO_TRACKER and us.DELETED_FLAG = 0 \n" +
                "LEFT JOIN T_SYS_COMPANY_DEPT s4 with(nolock) on s4.COMPANY_NUMBER=t.COMPANY_NO and t.APPLICANT_DEPT = s4.DEPT_NAME\n" +
                "where \n" +
                "m.BIZ_TABLE_NAME = 'T_FORM_FAPIAO_TRACING'\n" +
                "and m.TASK_STATUS IN ( '2', '3' ) \n" +
                "and not EXISTS (\n" +
                "  select 1 from T_FORM_FAPIAO_TASK fpt with (nolock) where fpt.BLONG_ACTIVITY = '5005609' and fpt.PARENT_FORM_ID = m.id\n" +
                ")\n" +
                "    AND m.LAST_UPDATE_DATE >= '?1' \n" +
                "    AND m.LAST_UPDATE_DATE < '?2')  AS S ON ( T.ORIGINAL_TASK_NUMBER = S.ORIGINAL_TASK_NUMBER ) \n" +
                "WHEN MATCHED THEN\n" +
                "  UPDATE SET \n" +
                "    T.SOURCING = S.SOURCING,T.type=S.type,T.amount=S.amount,T.amount_charge_of_store=S.amount_charge_of_store,T.amount_charge_of_company=S.amount_charge_of_company,\n" +
                "    T.amount_type=S.amount_type,T.supplier_no=S.supplier_no,T.supplier_name=S.supplier_name,T.tracer_type=S.tracer_type,T.tracer_id=S.tracer_id,\n" +
                "    T.order_id=S.order_id,T.trace_description=S.trace_description,T.payment_time=S.payment_time,T.payment_abstracts=S.payment_abstracts,\n" +
                "    T.voucher_no=S.voucher_no,T.acctg_period=S.acctg_period,T.company_code=S.company_code,\n" +
                "    T.status=S.status,T.original_voucher_no=S.original_voucher_no,T.store_name=S.store_name,T.fiscal_year=S.fiscal_year,\n" +
                "    T.original_task_number=S.original_task_number,T.bill_number=S.bill_number,T.paid_amount=S.paid_amount,T.underinvoicing_amount=S.underinvoicing_amount,T.short_voucher_no=S.short_voucher_no,\n" +
                "    T.creation_date=S.creation_date,T.closed_date=S.closed_date,T.LAST_UPDATE_DATE = getdate(),T.is_dimission=S.is_dimission\n" +
                "  WHEN NOT MATCHED BY TARGET THEN\n" +
                "  INSERT (SOURCING,type,amount,amount_charge_of_store,amount_charge_of_company,amount_type,supplier_no,supplier_name,tracer_type,pay_info,\n" +
                "  tracer_id,order_id,trace_description,remark,payment_time,payment_abstracts,voucher_no,acctg_period,company_code,cost_start_time,\n" +
                "  cost_end_time,status,original_voucher_no,store_name,payment_block,fiscal_year,original_task_number,bill_number,paid_amount,underinvoicing_amount,\n" +
                "  creation_date,closed_date,CREATED_DATE,LAST_UPDATE_DATE,task_no,is_dimission,short_voucher_no)\n" +
                "VALUES\n" +
                "  (S.SOURCING,S.type,S.amount,S.amount_charge_of_store,S.amount_charge_of_company,S.amount_type,S.supplier_no,S.supplier_name,S.tracer_type,S.pay_info,S.tracer_id,\n" +
                "  S.order_id,S.trace_description,S.remark,S.payment_time,S.payment_abstracts,S.voucher_no,S.acctg_period,S.company_code,S.cost_start_time,S.cost_end_time,S.status,\n" +
                "  S.original_voucher_no,S.store_name,S.payment_block,S.fiscal_year,S.original_task_number,S.bill_number,S.paid_amount,S.underinvoicing_amount,S.creation_date,\n" +
                "  S.closed_date,getdate(),getdate(),S.task_no,S.is_dimission,S.short_voucher_no); ";
    }

    private String getSql2() {
        return "insert into T_FCT_INVOICE_LIST(original_task_number,\n" +
                "                invoice_code,\n" +
                "                invoice_no,\n" +
                "                invoice_date,\n" +
                "                total_amount,\n" +
                "                total_tax,\n" +
                "                check_code,\n" +
                "                amount_without_tax,\n" +
                "                INVOICE_TYPE,\n" +
                "                offset_amount,\n" +
                "                CREATED_DATE,\n" +
                "                LAST_UPDATE_DATE,\n" +
                "                task_no\n" +
                "                )\n" +
                "select \n" +
                "m1.TASK_NO 'original_task_number', \n" +
                "d.invoice_code'invoice_code',\n" +
                "d.INVOICE_NO'invoice_no',\n" +
                "FORMAT(d.invoice_date, 'yyyyMMdd') 'invoice_date',\n" +
                "( CASE WHEN ISNUMERIC(d.INVOICE_AMOUNT)= 1 THEN \n" +
                "      CAST(replace(d.INVOICE_AMOUNT,',','') AS float  ) \n" +
                "    ELSE null END ) 'total_amount',\n" +
                "(case when (ISNUMERIC(d.INVOICE_AMOUNT_WITHOUT_TAX)= 0 or ISNUMERIC(d.INVOICE_AMOUNT)= 0)  then null else \n" +
                "(( CASE WHEN ISNUMERIC(d.INVOICE_AMOUNT)= 1 THEN \n" +
                "       CAST(replace(d.INVOICE_AMOUNT,',','') AS float ) \n" +
                "    ELSE 0 END )  - ( CASE WHEN ISNUMERIC(d.INVOICE_AMOUNT_WITHOUT_TAX)= 1 THEN \n" +
                "      CAST(replace(d.INVOICE_AMOUNT_WITHOUT_TAX,',','') AS float)\n" +
                "    ELSE 0 END ) ) end )'total_tax',\n" +
                " d.CHECK_CODE 'check_code',\n" +
                "( CASE WHEN ISNUMERIC(d.INVOICE_AMOUNT_WITHOUT_TAX)= 1 THEN \n" +
                "      CAST(replace(d.INVOICE_AMOUNT_WITHOUT_TAX,',','') AS float) \n" +
                "    ELSE null END )'amount_without_tax',\n" +
                "case when d.FAPIAO_TYPE = '30032' and d.IS_E_APPLICATION = 1 then 'se'\n" +
                "when d.FAPIAO_TYPE = '30032' and d.IS_E_APPLICATION != 1 then 's'\n" +
                "when d.FAPIAO_TYPE = '30033' and d.IS_E_APPLICATION = 1 then 'ce'\n" +
                "when d.FAPIAO_TYPE = '30033' and d.IS_E_APPLICATION != 1 then 'c'\n" +
                "when d.FAPIAO_TYPE = '500730186' and d.IS_E_APPLICATION = 1 then 'qs'\n" +
                "when d.FAPIAO_TYPE = '500730187' and d.IS_E_APPLICATION != 1 then 'qc'\n" +
                "when d.FAPIAO_TYPE is not null then 'r'\n" +
                "end 'INVOICE_TYPE',\n" +
                "( CASE WHEN ISNUMERIC(t.OFFSET_AMOUNT)= 1 THEN \n" +
                "       CAST(replace(t.OFFSET_AMOUNT,',','') AS float) \n" +
                "    ELSE null END ) 'offset_amount',\n" +
                "    GETDATE() 'CREATED_DATE',\n" +
                "    GETDATE() 'LAST_UPDATE_DATE',\n" +
                "m.task_no 'task_no'\n" +
                "from T_BAS_VOUCHER_TASK t with(nolock)\n" +
                " left join T_FORM_MAIN m with(nolock) on m.TASK_NO = t.TASK_NO and t.OFFSET_AMOUNT is not null\n" +
                " left join T_FORM_FAPIAO_TRACING t1 with(nolock) on t.VOUCHER_NO = t1.VOUCHER_NO\n" +
                " left join T_FORM_MAIN m1 with(nolock) on t1.PARENT_FORM_ID = m1.id\n" +
                " left join T_FORM_AP_DEAL d with(nolock) on m.ID = d.PARENT_FORM_ID\n" +
                "--  left join T_SYS_ENUM_VALUE v with(nolock) on d.FAPIAO_TYPE = v.ENUM_VALUE_NUM and v.ENUM_ITEM_CODE = 'INVOICE_TYPE'\n" +
                " where\n" +
                " m1.task_no is not null\n" +
                " and t.CREATED_DATE>='?1'\n" +
                " and t.CREATED_DATE<'?2' ;";
    }
}
